"use strict";

var sqlite3 = require('sqlite3').verbose();

var BookMark = {
    id: String,
    title: String,
    timestamp: String,
    url: String,
};

var dbName = 'data/BookMarks.sqlite3';

var db = new sqlite3.Database(dbName);


var BookMarkDAO = function() {};
module.exports = new BookMarkDAO();

BookMarkDAO.prototype.createTable = function createTable(callback) {
    var bookmarkSql = 'CREATE TABLE IF NOT EXISTS bookmark (id INTEGER PRIMARY KEY, title TEXT, timestamp INTEGER, url TEXT)';
    var clickSql = 'CREATE TABLE IF NOT EXISTS click (id INTEGER, timestamp INTEGER, ip TEXT)';
    db.run(bookmarkSql, function(err) {
        if (err) {
            err = 'Create bookmark Table Err: ' + err;
        } else {
            db.run(clickSql, function(err) {
                if (err) {
                    err = 'Create click Table Err: ' + err;
                }
            });
        };
        callback(err);
    });
};

BookMarkDAO.prototype.save = function(bookmark, callback) {
    var sql = "INSERT INTO bookmark select ?, ?, ?, ?  where not exists(select 1 from bookmark where id = ?)";
    var params = [
        bookmark.id, bookmark.title,
        bookmark.timestamp, bookmark.url, bookmark.id
    ];
    var stmt = db.prepare(sql);
    stmt.run(params, function(err) {
        callback(err);
    });
    stmt.finalize();
}

BookMarkDAO.prototype.saveAll = function(bookmarks, callback) {
    bookmarks.forEach(function(bookmark, err) {
        BookMarkDAO.prototype.save(bookmark, callback);
    });
}

BookMarkDAO.prototype.all = function(callback) {
    var results = [];
    db.all("SELECT * FROM bookmark order by timestamp desc",
        function(err, rows) {
            if (err) {
                callback(results, err);
            } else {
                if (rows.length > 0) {
                    rows.forEach(function(row, i) {
                        results.push(row);
                    });
                } else {
                    var err = "no data";
                }
                callback(results, err);
            };
        }
    );
};